import os
import sys

import xlwings as xw
import uuid

def read_excel(p_array,p_team,p_yield,p_factory):
    try:
        app = xw.App(visible=True,add_book=False)
        app.display_alerts = False
        app.screen_updating = False

        # 文件位置：filepath，打开test文档，然后保存，关闭，结束程序
        filepath = 'temp/applytemp.xlsx'
        wb = app.books.open(filepath)

        sht= wb.sheets['Sheet1']
        # rng = sht['A1:D38']
        # print(rng.value)
        #遍历按人数的array
        excel_cell=['g','h','i','j','k','l','m','n','o','p','q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN'];
        intindex=0;
        for p_v in p_array:
            int_p_v = int(p_v)
            startcell=excel_cell[intindex]+str(4)
            endcell = excel_cell[intindex] + str(35)
            rangestr=startcell+":"+endcell
            print(rangestr)
            cell_4_35_value = sht.range(rangestr).value
            #把每列填写的人数写入模板
            singcell = excel_cell[intindex] + str(3)
            sht.range(singcell).value = int_p_v
            startrow = 4;
            for v in cell_4_35_value:
                # 模板中的数据值
                int_c = 0;
                if (type(v) is float):
                    int_c = int(v)
                else:
                    int_c = 0
                g_row_string = excel_cell[intindex] + str(startrow)
                # print(g_row_string)
                sht.range(g_row_string).value = int_c * int_p_v
                startrow += 1
            intindex+=1
        intindex = 0;
        for p_v in p_team:
            int_p_v = int(p_v)
            startcell = excel_cell[intindex] + str(37)
            endcell = excel_cell[intindex] + str(42)
            rangestr = startcell + ":" + endcell
            cell_startcell_endcell_value = sht.range(rangestr).value
            # 把每列填写的人数写入模板
            singcell = excel_cell[intindex] + str(36)
            sht.range(singcell).value = int_p_v
            startrow = 37;
            for v in cell_startcell_endcell_value:
                # 模板中的数据值
                int_c = 0;
                if (type(v) is float):
                    int_c = int(v)
                else:
                    int_c = 0
                g_row_string = excel_cell[intindex] + str(startrow)
                # print(g_row_string)
                sht.range(g_row_string).value = int_c * int_p_v
                startrow += 1
            intindex += 1
        #按产量进行，根据模板把数据填充
        intindex = 0;
        for p_v in p_yield:
            int_p_v = int(p_v)
            startcell = excel_cell[intindex] + str(44)
            endcell = excel_cell[intindex] + str(45)
            rangestr = startcell + ":" + endcell
            cell_startcell_endcell_value = sht.range(rangestr).value
            # 把每列填写的人数写入模板
            singcell = excel_cell[intindex] + str(43)
            sht.range(singcell).value = int_p_v
            startrow = 44;
            for v in cell_startcell_endcell_value:
                # 模板中的数据值
                int_c = 0;
                if (type(v) is float):
                    int_c = int(v)
                else:
                    int_c = 0
                g_row_string = excel_cell[intindex] + str(startrow)
                # print(g_row_string)
                sht.range(g_row_string).value = int_c * int_p_v
                startrow += 1
            intindex += 1
        # 按厂数，根据模板把数据填充
        intindex = 0;
        for p_v in p_factory:
            int_p_v = int(p_v)
            startcell = excel_cell[intindex] + str(47)
            cell_startcell_endcell_value = sht.range(startcell).value
            # 把每列填写的人数写入模板
            singcell = excel_cell[intindex] + str(46)
            sht.range(singcell).value = int_p_v
            # 模板中的数据值
            int_c = 0;
            if (type(cell_startcell_endcell_value) is float):
                int_c = int(cell_startcell_endcell_value)
            else:
                int_c = 0
            sht.range(startcell).value = int_c * int_p_v
            intindex += 1

        check_and_creat_dir('D:\\downFile\\')
        filename = uuid.uuid4().hex
        filepath = 'D:\downFile/' + filename + '.xlsx'
        wb.save(filepath)
        # wb.save()
        wb.close();
        app.quit();
        return "文件生成成功"
        #print('编辑成功')
    except OSError as err:
        wb.close();
        app.quit();
        return "OS error: {0}".format(err)
        #print("OS error: {0}".format(err))
    except ValueError:
        wb.close();
        app.quit();
        return  "Could not convert data to an integer."
        #print("Could not convert data to an integer.")
    except:
        wb.close();
        app.quit();
        return "Unexpected error:", sys.exc_info()[0]
        #print("Unexpected error:", sys.exc_info()[0])
def creatExcel():
    app = xw.App(visible=True, add_book=False)
    wb = app.books.add()
    #wb.save(r'd:\test.xlsx')
    filename=uuid.uuid4().hex
    filepath = 'newExcel/'+filename+'.xlsx'
    wb.save(filepath)
    wb.close()
    app.quit()
    print('创建成功')


def check_and_creat_dir(path):
      # 去除首位空格
    path=path.strip()
    # 去除尾部 \ 符号
    path=path.rstrip("\\")
    # 判断路径是否存在
    # 存在     True
    # 不存在   False
    isExists=os.path.exists(path)
    # 判断结果
    if not isExists:
        os.makedirs(path)
        return True
    else:
        return False
# if __name__ == "__main__":
#     read_excel(10)
